<?php include 'countryCodeByIP.function.php'; ?>
<html>
<head>
<title>Stats</title>
<style type="text/css">
body {
   font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
   color: #246;
   margin: 30px;
}

img {
   padding-right: 5px;
}

h1 {
   color: #fff;
   background-color: #246;
   padding: 5px 10px;
   margin: 0;
   font-size: 20pt;
}

h1 p.powered-by {
   display: block;
   float: right;
   margin-top: 0;
   font-size: 8pt;
   text-align: center;
   width: 75px;
}

h1 p.powered-by .title {
   font-size: 11pt;
}

h2 {
   font-size: 16pt;
   padding: 0;
   margin: 0;
}

a {
   color: #246;
}

a:hover {
   color: #69c;
}

div.content {
   padding: 10px;
}

table {
   width: 100%;
}

table td {
   vertical-align: top;
}

table.decorated {
   font-size: 10pt;
   background: #fff;
   width: 100%;
   border-collapse: collapse;
   text-align: left;
   margin-bottom: 20px;
}

table.decorated th {
   font-size: 12pt;
   font-weight: normal;
   color: #246;
   padding: 10px 8px;
   border-bottom: 2px solid #069;
}

table.decorated td {
   border-bottom: 1px solid #069;
   padding: 6px 8px;
}

table.decorated tbody tr:hover * {
   color: #69c;
}
</style>
</head>
<body>
<?php

// open the database
$db = new PDO('sqlite:Counter.db');

if (!isset ($_GET['cid']) || !is_numeric($_GET['cid'])) {
    echo '<h1>Counter<p class="powered-by">Powered by<br /><span class="title">BlueStats</span></p></h1><div class="content">';
    // print overview of counters
    $query = $db->query("SELECT * FROM Counters ORDER BY Id");
    if(!$query) {
        echo '<p>No counters active, make sure you have executed the <a href="setup.php">setup.php</a>.</p>';
    } else {
        echo '<table class="decorated"><thead><tr><th>Counter ID</th><th>Total Hits</th><th>&nbsp;</th></tr></thead><tbody>';
        while ($entry = $query->fetchObject()) {
            echo '<tr><td>' . $entry->Id . '</td><td>' . $entry->Counter . '</td><td><a href="?cid=' . $entry->Id . '">Details</a></td></tr>'; 
        } 
        echo '</tbody></table>';
    }
} else {
    // print counter details
    echo '<h1>Counter details: ' . $_GET['cid'] . '<p class="powered-by">Powered by<br /><span class="title">BlueStats</span></p></h1><div class="content">';
    
    echo '<table><tr><td>'; # col 1

    // popular search keywords
    echo '<h2>Top 10 searches</h2>';
    echo '<table class="decorated"><thead><tr><th>Search Keywords</th><th>Hits</th></tr></thead><tbody>';
    $query = $db->query("SELECT SearchKeywords, COUNT(Id) AS Hits FROM Hits WHERE Counter = " . $_GET['cid'] . " AND SearchKeywords != '' GROUP BY SearchKeywords ORDER BY Hits DESC LIMIT 10");
    if (!$query) {
        echo '<p>Sorry, no results found.</p>';
    } else {
        while ($entry = $query->fetchObject()) {
            echo '<tr><td>' . $entry->SearchKeywords . '</td><td>' . $entry->Hits . '</td></tr>';
        }
    }
    echo '</tbody></table>';

    echo '</td><td>'; // #col 2
    echo '<h2>Montly statistics</h2>';
    echo '<table class="decorated"><thead><tr><th>Month</th><th>Total Hits</th><th>Unique Hits</th><th>Unique Visitors</th></tr></thead><tbody>';
    for ($i = date('m'); $i > 0; $i--) {
        $dateStart = new DateTime(date('Y') . '-' . $i . '-1');
        $dateEnd = new DateTime(date('Y') . '-' . $i . '-1');
        $dateEnd->modify('+1 month');
        $timeStart = $dateStart->format('U');
        $timeEnd = $dateEnd->format('U');

        // date
        echo '<tr><td>' . $dateStart->format('F Y') . '</td>';

        // count hits, unique hits and unique visitors for one month
        $query = $db->query("SELECT SUM(tmpHits) AS Hits, SUM(tmpUniqueHits) AS UniqueHits, COUNT(tmpUniqueHits) AS UniqueVisitors FROM (SELECT COUNT(RequestUri) AS tmpHits, COUNT(DISTINCT RequestUri) AS tmpUniqueHits FROM Hits WHERE Counter = " . $_GET['cid'] . " AND Timestamp >= " . $timeStart . " AND Timestamp < " . $timeEnd . " GROUP BY RemoteAddr)");
        $entry = $query->fetchObject();        

        // total hits
        echo '<td>' . number_format($entry->Hits) . '</td>';

        // unique hits
        echo '<td>' . number_format($entry->UniqueHits) . '</td>';

        // unique visitors
        echo '<td>' . number_format($entry->UniqueVisitors) . '</td>';
        echo '</tr>';
    }
    echo '</tbody></table>';

    echo '</td><td>'; // #col 3

    echo '</td></tr></table>';
    // raw hits
    echo '<h2>Latest 100 hits</h2>';
    $query = $db->query("SELECT * FROM Hits WHERE Counter = " . $_GET['cid'] . " ORDER BY Timestamp DESC LIMIT 100");
    if (!$query) {
        echo '<p>Sorry, no hits found. Make sure you have installed the counter on your site and you have a visitor.</p>';
    } else {
        echo '<table class="decorated"><thead><tr><th>Time</th><th>RemoteAddr</th><th>RequestUri</th><th>Referer</th><th>Search Keywords</th><th>UserAgent</th></tr></thead><tbody>';
        
        while ($entry = $query->fetchObject()) {
            $_endUserAgent = strpos($entry->UserAgent, ')');
            if ($_endUserAgent > 0) {
                $_endUserAgent++;
            }
            $country = countryCodeByIP($entry->RemoteAddr);
            $flag = $country;
            if (!$flag || !file_exists(dirname(__file__) . '/flags/' . $flag . '.gif')) {
                $flag = 'noflag';
            } 
            echo '<tr><td>' . date('Y-m-d H:i:s', $entry->Timestamp) . '</td>' .
            '<td><img src="flags/' . $flag . '.gif" alt="' . $country . '" title="' . $country . '" />' . $entry->RemoteAddr . '</td>' .
            '<td>' . $entry->RequestUri . '</td>' .
            '<td><a href="' . $entry->Referer . '" title="' . $entry->Referer . '">' . substr($entry->Referer, 0, @strpos($entry->Referer, '/', 8)) . '</a></td>' . 
            '<td title="' . $entry->SearchKeywords . '">' . substr($entry->SearchKeywords, 0, 100) . '</td>' .
            '<td title="' . $entry->UserAgent . '">' . substr($entry->UserAgent, 0, $_endUserAgent) . '</td>' .
            '</tr>';
        }

        echo '</tbody></table>';
    }
}

// close the database connection
$db = NULL;
?>
</div>
</body>
</html>
